Temporal database Totally Explained

Everything about Temporal Database totally explained

A temporal database is a database with built-in time aspects, for example a temporal data model and a temporal version of structured query language. More specifically the temporal aspects usually include valid-time and transaction-time. These attributes go together to form bitemporal data.

Valid time denotes the time period during which a fact is true with respect to the real world.
Transaction time is the time period during which a fact is stored in the database.
Bitemporal data combines both Valid and Transaction Time. Note that these two time periods don't have to be the same for a single fact. Imagine that we come up with a temporal database storing data about the 18th century. The valid time of these facts is somewhere between 1700 and 1799, whereas the transaction time starts when we insert the facts into the database, for example, January 21, 1998.

Example

For illustration, we'll take data from the following short biography of a fictional man John Doe. John Doe was born on April 3rd, 1975 in the Kids Hospital of Medicine County, as son of Jack Doe and Jane Doe who lived in Smallville. Jack Doe proudly registered the birth of his first-born on April 4th, 1975 at the Smallville City Hall. John grew up as a joyful boy, turned out to be a brilliant student and graduated with honors in 1993. After graduation he went to live on his own in Bigtown. Although he moved out on August 26th, 1994, he forgot to register the change of address officially. It was only at the turn of the seasons that his mother reminded him that he'd to register, which he did a few days later on December 27, 1994. Although John had a promising future, his story ends tragically. John Doe was accidentally hit by a truck on April 1st, 2001. The coroner reported his date of death on the very same day.

Using a Standard Database

In order to store the life of John Doe in a (non-temporal) database table we use this table Person (Name, Address). In order to simplify we define Name as the primary key of Person.
   John's father officially reported birth on April 4th, 1975. This means that a Smallville official, inserted the following entry in the database on this date: Person (John Doe, Smallville) Note that the date itself isn't stored in the database.
   After graduation John moves out, but forgets to register his new address. John's entry in the database isn't changed until December 27, 1994, when he finally enters Bigtown's city hall. A Bigtown official updates his address in the database. The Person table now contains Person (John Doe, Bigtown) Note that the information of John living in Smallville has been overwritten. There is no way to retrieve that information from the database. Any official accessing the database on December 28, 1994 would be told that John lives in Bigtown. More technically: if a computer scientist ran the query SELECT ADDRESS FROM PERSON WHERE NAME='John Doe' on December 26, 1994, the result would be: Smallville. Running the same query 2 days later would result in Bigtown.
   Until his tragic death the database would state that he lived in Bigtown. On April 1, 2001 the coroner deletes the John Doe entry from the database. Running the above query would return no result at all.

Date	hat happened in the real world	atabase Action	hat the database shows
April 3rd, 1975	John is born	Nothing	There is no person called John Doe
April 4th, 1975	John's father officially reports John's birth	Inserted:Person(John Doe, Smallville)	John Doe lives in Smallville
August 26, 1994	After graduation, John moves to Bigtown, but forgets to register his new address	Nothing	John Doe lives in Smallville
December 26, 1994	Nothing	Nothing	John Doe lives in Smallville
December 27, 1994	John registers his new address	Updated:Person(John Doe, Bigtown)	John Doe lives in Bigtown
April 1, 2001	John dies	Deleted:Person(John Doe)	There is no person called John Doe

Bitemporal Relations

A bi-temporal relation contains both valid and transaction time. This is good because it provides both temporal rollback and historical information. Temporal rollback (eg: "In 1992, where did the database believe John lived?") is provided by the transaction time. Historical information (eg: "Where did John live in 1992?") can be derived from valid time. The answers to these example questions may not be identical - the database may have been altered since 1992, causing the queries to produce different results.

Valid Time

Valid time is the time for which a fact is true in the real world. In the example above, the Person table gets two extra fields, Valid-From and Valid-To, specifying when a person's address was valid in the real world. On April 4th, 1975 John's father proudly registered his son's birth. An official will then insert a new entry to the database stating that John lives in Smallville from the April, 3rd. Notice that although the data was inserted on the 4th, the databases states that the information is valid since the 3rd. The official doesn't yet know if or when John will ever move to a better place so in the database the Valid-To is filled with infinity (∞). Resulting in this entry in the database:
    Person(John Doe, Smallville, 3-Apr-1975, ∞).
   December 27, 1994 John reports his new address in Bigtown where he's been living since August 26th, 1994. The Bigtown official doesn't change the address of the current entry of John Doe in the database. He adds a new one:
Person (John Doe, Big Town, 26-Aug-1994, ∞).
   The original entry Person (John Doe, Smallville, 3-Apr-1975, ∞) is then updated (not removed!). Since it's now known that John stopped living in Smallville on August 26, 1994 the Valid-To entry can be filled in. The database now contains two entries for John Doe
    Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994). Person(John Doe, Bigtown, 26-Aug-1994, ∞).
   When John dies the database is once more updated. The current entry will be updated stating that John doesn't live in the Bigtown any longer. No new entry is being added because officials never report heaven as a new address. The database now looks like this
    Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994). Person(John Doe, Bigtown, 26-Aug-1994, 1-Apr-2001).

Transaction Time

Transaction time is the time a transaction was made. This enables queries that show the state of the database at a given time. Two more fields are added to the Person table: Transaction-From and Transaction-To. Transaction-From is the time a transaction was made, and Transaction-To is the time that the transaction was superseded (or infinity if it hasn't yet been superseded).
   What happens if the person's address as stored in the database is incorrect? Suppose an official accidentally entered the wrong address or date? Or, suppose the person lied about their address for some reason. Upon discovery of the fact, the officials go back and update the database.
   For example, from 1-Jun-1995 to 3-Sep-2000 John Doe moved to Beachy. But, to avoid paying Beachy's exorbitant residence tax, he never reported it to the authorities. Later, it's discovered on 2-Feb-2001, during a tax investigation that he was in fact in Beachy during these dates, so they update the database as follows:
Person(John Doe, Bigtown, 26-Aug-1994, 1-Jun-1995). Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000). Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001).
   So the existing two records are updated and a new record is inserted recording his residence in Beachy.
   However, this leaves no record that the database ever claimed that he lived in Bigtown during 1-Jun-1995 to 3-Sep-2000. Which might be important for say auditing reasons (or to use as evidence in the official's tax investigation.) This is where transaction time comes in. We record in each record when it was entered and when it was superseded. Thus we get something like this:
Person(John Doe, Smallville, 3-Apr-1975, ∞, 4-Apr-1975, 27-Dec-1994). Person(John Doe, Smallville, 3-Apr-1975, 26-Aug-1994, 27-Dec-1994, ∞ ). Person(John Doe, Bigtown, 26-Aug-1994, ∞, 27-Dec-1994, 2-Feb-2001 ). Person(John Doe, Bigtown, 26-Aug-1994, 1-Jun-1995, 2-Feb-2001, ∞ ). Person(John Doe, Beachy, 1-Jun-1995, 3-Sep-2000, 2-Feb-2001, ∞ ). Person(John Doe, Bigtown, 3-Sep-2000, ∞, 2-Feb-2001, 1-Apr-2001 ). Person(John Doe, Bigtown, 3-Sep-2000, 1-Apr-2001, 1-Apr-2001, ∞ ).
   So we record not only changes in what happened at different times, but also changes in what was officially recorded at different times.

Books

C.J. Date, Hugh Darwen, Nikos Lorentzos (2002). Temporal Data & the Relational Model, First Edition (The Morgan Kaufmann Series in Data Management Systems); Morgan Kaufmann; 1st edition; 422 pages. ISBN 1-55860-855-9.

Joe Celko (2005). Joe Celko's SQL for Smarties: Advanced SQL Programming (The Morgan Kaufmann Series in Data Management); Morgan Kaufmann; 3rd edition; 808 pages. ISBN 0-12-369379-9.—Chapters 4 and 29 in particular discuss temporal issues.

Richard T. Snodgrass, Christian S. Jensen (1999). (Morgan Kaufmann Series in Data Management Systems); Morgan Kaufmann; 504 pages; ISBN 1-55860-436-7Further Information

Get more info on 'Temporal Database'.

External Link Exchanges

Do you know how hard it is to get a link from a large encyclopaedia? Well we're different and will prove it. To get a link from us just add the following HTML to your site on a relevant page:

<a href="http://temporal_database.totallyexplained.com">Temporal database Totally Explained</a>

Then simply click through this link from your web page. Our crawlers will verify your link, extract the title of your web page and instantly add a link back to it. If you like you can remove the words Totally Explained and embed the link in article text.
As long as your link remains in place, we'll keep our link to you right here. Please play fair - our crawlers are watching. Your site must be closely related to this one's topic. Any kind of spamming, dubious practises or removing the link will result in your link from us being dropped and, potentially, your whole site being banned.